# -*- encoding: utf-8 -*-
import math

from odoo import models, fields, api, _
from odoo.addons import base_cw
from odoo.exceptions import UserError


# from odoo.tools.float_utils import float_round, float_compare


class account_voucher_category(models.Model):
    _name = 'account.voucher.category'
    _inherit = ['mail.thread']
    _description = u'会计凭证类别'

    code = fields.Char(u'编码', tracking=True, required=True)
    name = fields.Char(u'名称', translate=False, tracking=True, required=True)
    note = fields.Char(u'备注', )
    active = fields.Boolean(u'启用', default=True)
    is_system_created = fields.Boolean(u'为系统初识化资料', default=False,
                                       help=u'标示此笔资料为系统初识化所创建,不可以删除')
    type = fields.Selection([('sale', '销售',),
                             ('purchase', '采购'),
                             ('cash', '现金'),
                             ('bank', '银行'),
                             ('general', '手工'),
                             ('bill', '票据'),
                             ], string='来源', default='general', required=True)

    voucher_word = fields.Char('凭证字', required=True, default='记')
    voucher_name = fields.Char('凭证名称', required=True, default='记账凭证')
    ir_seq_id = fields.Many2one('ir.sequence', string='凭证序号模板', required=True)
    pre_code = fields.Char('凭证序号前缀', related='ir_seq_id.prefix')

    _sql_constraints = [('code_unique', 'unique (code)', u'会计凭证类别编码不能重复!'),
                        ('name_unique', 'unique (name)', u'会计凭证类别名称不能重复!'), ]


class account_voucher(models.Model):
    _name = 'account.voucher'
    _inherit = ['mail.thread']
    _description = u'会计凭证'

    @api.depends('account_id')
    def _get_sub_account_ids(self):
        account_obj = self.env['cncw.account']
        self.sub_account_ids = account_obj.get_sub_account_ids(self.account_id)

    @api.model
    def _get_default_voucher_category(self):
        return self.env.ref('cncw_ledger.account_voucher_category_01')

    @api.depends('line_ids', 'line_ids.debit', 'line_ids.credit')
    def _compute_debit_credit(self):
        for record in self:
            record.total_debit = sum(record.line_ids.mapped('debit'))
            record.total_credit = sum(record.line_ids.mapped('credit'))

    voucher_paging = fields.Integer(string="凭证页号", default=0)
    parent_id = fields.Many2one('account.voucher', string='上一张凭证',
                                compute="_compute_parent_id", store=True, readonly=False)
    child_ids = fields.One2many('account.voucher', 'parent_id', string='关联凭证')
    parent_path = fields.Char(index=True)
    attachment_ids = fields.Many2many('ir.attachment', string='附件')
    attachment_count = fields.Integer('附件数', default=0, store=True, compute='_compute_attachment_count', readonly=True)
    name = fields.Char(u'单据编号', required=True, default='New', copy=False, readonly=False)
    date = fields.Date(u'单据日期', default=fields.Date.context_today, required=True)
    voucher_category_id = fields.Many2one('account.voucher.category', u'凭证类别', default=_get_default_voucher_category,
                                          required=False, ondelete="restrict", tracking=True)
    voucher_word = fields.Char(string='凭证字', related='voucher_category_id.voucher_word')
    note = fields.Text(u'备注')
    origin = fields.Char(u'单据来源')
    confirm_date = fields.Datetime(u'确认日', readonly=True)
    confirm_user_id = fields.Many2one('res.users', u'确认人', required=False, readonly=True)
    audit_date = fields.Datetime(u'审核日', readonly=True)
    audit_user_id = fields.Many2one('res.users', u'审核人', required=False, readonly=True)
    company_id = fields.Many2one('res.company', string=u'公司', change_default=True,
                                 required=True, readonly=True, states={'draft': [('readonly', False)]},
                                 default=lambda self: self.env.company)
    state = fields.Selection([('draft', u'草稿'),
                              ('confirmed', u'已确认'),
                              ('audited', u'已审核'),
                              ('cancel', u'已取消'), ], u'单据状态', required=True, default='draft',
                             tracking=True)
    line_ids = fields.One2many('account.voucher.line', 'master_id', u'凭证明细', copy=True, tracking=True)
    is_profit_loss_voucher = fields.Boolean(u'期结转损益凭证', default=False)

    dc_type = fields.Selection(base_cw.public.DC_TYPE, u'借贷', default='D', store=False)
    account_id = fields.Many2one('cncw.account', u'会计科目', ondelete="set null", store=False)
    sub_account_id = fields.Many2one('res.partner', u'辅助核算', ondelete="set null", store=False)
    sub_account_type = fields.Selection(base_cw.public.SUB_ACCOUNT_TYPE, u'辅助核算类型', ondelete="set null",
                                        related='account_id.sub_account_type', readonly=True)
    sub_account_ids = fields.Many2many('res.partner', compute='_get_sub_account_ids', string=u'可选辅助核算')
    remark = fields.Char(u'摘要', store=False)
    remark_id = fields.Many2one('account.voucher.remark', u'常用', ondelete="set null", store=False)
    total_debit = fields.Float(u'借方金额', compute='_compute_debit_credit', digits='Amount', store=True)
    total_credit = fields.Float(u'贷方金额', compute='_compute_debit_credit', digits='Amount', store=True)
    period_id = fields.Many2one('account.period', u'期别', compute="compute_period_id", store=True)
    done_user_id = fields.Many2one('res.users', u'完成人')
    done_date = fields.Datetime(u'完成日', readonly=True)
    catatory_type = fields.Selection(related='voucher_category_id.type', string='来源')
    account_category_id = fields.Many2one('account.voucher.category', string='凭证类别')
    total_of_china = fields.Char(string='总计(大写)', compute='_compute_total_of_china')
    account_pay_id = fields.Many2one('account.pay', string='付款单')
    account_receive_id = fields.Many2one('account.receive', string='收款单')
    voucher_template_id = fields.Many2one('account.voucher.template', string='凭证模板')

    @api.depends('date')
    def compute_period_id(self):
        for record in self:
            if record.date:
                period = self.env['account.period'].search([('date_start', '<=', record.date),('date_stop', '>=', record.date)], limit=1)
                if period:
                    record.period_id = period.id

    # @api.depends('voucher_category_id','voucher_category_id.ir_seq_id')
    # def _compute_name(self):
    #     for record in self:
    #         # voucher_category = self.env['account.voucher.category'].browse([vals.get('voucher_category_id')])
    #         # if record.name == 'New':
    #         code = self._name
    #         if record.voucher_category_id and record.voucher_category_id.ir_seq_id:
    #             code = record.voucher_category_id.ir_seq_id.code
    #         record.name = self.env['ir.sequence'].next_by_code(code)
    #         record.flush()

    @api.depends('voucher_paging')
    def _compute_parent_id(self):
        for record in self:
            if record.voucher_paging <= 0:
                record.parent_id = False

    @api.depends('total_debit')
    def _compute_total_of_china(self):
        for record in self:
            res = base_cw.public.get_chinese_money(record.total_debit)
            record.total_of_china = res

    @api.depends('attachment_ids')
    def _compute_attachment_count(self):
        for record in self:
            if record.attachment_ids:
                record.attachment_count = len(record.attachment_ids)
            else:
                record.attachment_count = 0
            # record.flush()

    @api.model
    def create(self, vals):
        # base_cw.public.generate_voucher_no(self, vals)
        # res = super(account_voucher, self).create(vals)
        code = self._name
        voucher_category = self.env['account.voucher.category'].browse([vals.get('voucher_category_id')])
        if voucher_category and voucher_category.ir_seq_id:
            code = voucher_category.ir_seq_id.code
        # result_code = self.env['ir.sequence'].next_by_code(code)
        # vals['name'] = self.voucher_name_break_change(result_code,voucher_category.ir_seq_id.padding)
        vals['name'] = self.env['ir.sequence'].next_by_code(code)
        return super(account_voucher, self).create(vals)

    def voucher_name_break_change(self, code, padding):
        """凭证编号断号处理
        :param code 产生的新号码
        :param padding ir.sequence的序列号
        """
        # 对通过ir.sequence获取到的编号进行处理分成规则编号和序列编号
        code_first = code[:-padding]  # 规则编号
        code_second = code[-padding:]  # 序列编号
        # self._cr.execute("""select name from account_voucher where name like '{code_first}%' and state !='cancel' """.format(code_first=code_first))
        # all_list = self._cr.fetchall()
        voucher_name_list = self.search([('name', 'like', code_first),
                                         ('state', '!=', 'cancel')], order='name').mapped('name')
        voucher_name_second_list = sorted([voucher_name[-padding:] for voucher_name in voucher_name_list])
        d_num = '%0{}d'.format(padding)
        result_second_code = code_second
        if not voucher_name_list:
            return "%s%s" % (code_first, d_num % (1))
        for i in range(len(voucher_name_second_list)):
            if len(voucher_name_second_list) > 1:
                if i != 0:
                    bigger_code = int(voucher_name_second_list[i])
                    small_code = int(voucher_name_second_list[i - 1])
                    # 判断 前后编码是否存在断号,
                    if bigger_code - small_code > 1:
                        # 若存在断号，比对小的值与传进来的值，若绝对值大于1，说明仍旧不是连续的号，则小的值加1替换掉传进来的值
                        if abs(small_code - int(result_second_code)) > 1:

                            result_code = "%s%s" % (code_first, d_num % (small_code + 1))
                            return result_code
                        else:
                            return code

            else:
                if int(voucher_name_second_list[i]) == 1:
                    return "%s%s" % (code_first, d_num % (i + 1))
                else:
                    return "%s%s" % (code_first, d_num % 1)

    def write(self, vals):
        if vals.get('voucher_category_id'):
            code = self._name
            voucher_category = self.env['account.voucher.category'].browse([vals.get('voucher_category_id')])
            if voucher_category and voucher_category.ir_seq_id:
                code = voucher_category.ir_seq_id.code
            vals['name'] = self.env['ir.sequence'].next_by_code(code)
        return super(account_voucher, self).write(vals)

    def copy(self, default=None):
        self.ensure_one()
        default = default or {}
        default.update(name='New',
                       state='draft',
                       origin='',
                       confirm_date=None,
                       confirm_user_id=False,
                       audit_user_id=False,
                       audit_date=None)
        return super(account_voucher, self).copy(default)

    @api.onchange('remark_id')
    def onchangge_remark_id(self):
        if self.remark_id:
            self.remark = self.remark_id.name

    @api.onchange('date')
    def onchange_date(self):
        if self.date:
            period_id = self.env['account.period'].search(
                [('date_start', '<=', self.date), ('date_stop', '>=', self.date)], limit=1)
            self.period_id = period_id and period_id.id or False

    def _post_validate(self):
        for x in self:
            if x.line_ids:
                if not all([x.company_id.id == x.company_id.id for x in x.line_ids]):
                    raise UserError(_(u"明细与主档的公司必须一致."))
            else:
                raise UserError(_(u'没有凭证明细不能确认！'))
                # self.assert_balanced() 会计凭证确认时不验证
                # return self._check_lock_date()

    def _check_lock_date(self):
        for move in self:
            lock_date = max(move.company_id.period_lock_date, move.company_id.fiscalyear_lock_date)
            if self.user_has_groups('account.group_account_manager'):
                lock_date = move.company_id.fiscalyear_lock_date
            if move.date <= lock_date:
                if self.user_has_groups('account.group_account_manager'):
                    message = _("You cannot add/modify entries prior to and inclusive of the lock date %s") % (
                        lock_date)
                else:
                    message = _(
                        "You cannot add/modify entries prior to and inclusive of the lock date %s. Check the company settings or ask someone with the 'Adviser' role") % (
                                  lock_date)
                raise UserError(message)
        return True

    @api.model
    def period_state_check(self):
        self.env['account.period'].period_state_check('account.voucher', date=self.date, belongs_to_module='gl')

    def assert_balanced(self):
        """判断账是否做平了"""
        if not self.ids:
            return True
        prec = self.env['decimal.precision'].precision_get('Amount')
        # 正则小数几位
        f_rule = '%.{}f'.format(max(5, prec))
        self._cr.execute("""
                SELECT  master_id
                  FROM  account_voucher_line
                 WHERE  master_id in %s
              GROUP BY  master_id
                HAVING  abs(sum(debit) - sum(credit)) > %s
            """, (tuple(self.ids), float(f_rule % 10 ** (-max(5, prec)))))  # 默认取六位小数  10 ** (-max(5, prec))
        if sum(self.line_ids.mapped('debit')) == 0 and sum(self.line_ids.mapped('credit')) == 0:
            raise UserError(_(u"正式凭证借贷不能都为0，请检查."))
        vals = self._cr.fetchall()
        if len(vals) != 0:
            raise UserError(_(u"正式凭证借贷不平，请检查."))
        return True

    def action_confirm(self):
        """
        :return:
        """
        for record in self:
            if record.state == 'draft':
                for line in record.line_ids:
                    line.need_sub_account_id(line.account_id.id, line.sub_account_lines_str)
                self._cr.commit()
                record._post_validate()
                record.assert_balanced()
                state = 'confirmed'

                record.write(dict(confirm_user_id=self._uid,
                                  confirm_date=fields.datetime.utcnow(),
                                  state=state))

    # 取消确认
    def action_cancel_confirm(self):
        self.ensure_one()
        state = 'draft'
        self.write(dict(confirm_user_id=False,
                        confirm_date=None,
                        state=state))

    # 审核
    def action_audit(self):
        """
        :return:
        """
        # self.ensure_one()
        for voucher in self:
            voucher.assert_balanced()
            if voucher.state == 'confirmed':
                voucher.period_state_check()
                state = 'audited'
                voucher.write(dict(done_user_id=self.env.user.id,
                                   done_date=fields.datetime.utcnow(),
                                   state=state))

    # 取消审核
    def action_cancel_audit(self):
        """
        :return:
        """
        for voucher in self:
            voucher.period_state_check()
            state = 'draft'
            voucher.write(dict(confirm_user_id=False,
                               confirm_date=None,
                               audit_user_id=False,
                               audit_date=None,
                               done_user_id=False,
                               state=state))

    # 取消凭证
    def action_cancel(self):
        if 1 > 2:
            raise UserError(_(u'有上游单据，不可以取消!'))
        for voucher in self:
            state = 'cancel'
            voucher.write(dict(confirm_user_id=False,
                               confirm_date=None,
                               done_user_id=False,
                               audit_user_id=False,
                               done_date=None,
                               state=state))

    # 由取消改为草稿
    def action_cancel_to_draft(self):
        self.ensure_one()
        state = 'draft'
        self.write(dict(state=state))

    def unlink(self):
        for record in self:
            if record.state != 'cancel':
                raise UserError(_(u'凭证不可以删除，可取消凭证。'))
        return super(account_voucher, self).unlink()

    def fix_voucher_name(self):
        """修改凭证编号"""
        return {
            'name': '修改凭证编号',
            'type': 'ir.actions.act_window',
            'view_mode': 'form',
            'res_model': 'wizard.fix.voucher.name',
            'target': 'new',
            'context': {'default_voucher_id': self.id}
        }

    def all_button_voucher(self):
        """批量确认审核凭证"""
        for record in self:
            # if not record.workorder_id:
            record.action_confirm()
            record.action_audit()

    def init(self):
        self._cr.execute("""
CREATE OR REPLACE FUNCTION create_profit_loss_voucher(period_id_t integer,
                                                                  company_id_t integer,
                                          uid integer) RETURNS void AS $$
            --DROP FUNCTION create_profit_loss_voucher(integer,integer)
            -- select create_profit_loss_voucher(15,1,1)
            -- select *  from account_period order by id

            -- 月末结转损益
            DECLARE
                   master_id_t integer;
                   name_t character varying;
                   rec record;
                   rec_line record;
                   seq_t integer;
                   date_start_t TIMESTAMP;
                   date_stop_t TIMESTAMP;
                   currency_id_t integer;
            begin
                    select date_start,date_stop into date_start_t,date_stop_t from account_period where id=period_id_t;
                    select currency_id into currency_id_t from res_company where id=company_id_t;

update account_voucher_template set total_amount=0
where coalesce(is_profit,'f')='t';
update account_voucher_template_line a set amount=0
  from account_voucher_template b
 where a.master_id=b.id
   and coalesce(b.is_profit,'f')='t';


   update account_voucher_template_line aa set amount=bb.amount
   from (select a.account_id,c.dc_type,
		   sum(coalesce(lc_debit,0)) as debit,
		   sum(coalesce(lc_credit,0)) as credit,
		   sum(coalesce(lc_debit,0)-coalesce(lc_credit,0))*(case when c.dc_type='D' then 1 else -1 end) as amount
	  from account_voucher_line a left join account_voucher b on a.master_id=b.id
				      left join cncw_account c on a.account_id=c.id
		   ,(select a.account_id,b.code
		       from account_voucher_template_line a left join account_voucher_template b on a.master_id=b.id
		      where coalesce(b.is_profit,'f')='t') d
	 where b.period_id=period_id_t
	   and b.state='audited'
	   and b.is_profit_loss_voucher='f'
	   and c.account_category in ('5','6','7') --损益类
	   and d.account_id=a.account_id
	 group by a.account_id,c.dc_type
   ) bb
   where aa.account_id=bb.account_id;

   update account_voucher_template a set total_amount=coalesce(e.amount,0)
     from (select d.id,sum(coalesce(amount,0)) as amount
             from account_voucher_template_line c left join account_voucher_template d on c.master_id=d.id
             where coalesce(d.is_profit,'f')='t'
             group by d.id) e
    where a.id=e.id;

    for rec in select id,code,name,voucher_category_id,current_year_profit_account_id,dc_type,total_amount
                  from account_voucher_template
                 where coalesce(is_profit,'f')='t'
                   and coalesce(total_amount,0)<>0
                 order by code

    loop
	   select 'FG'|| EXTRACT(YEAR from date_stop_t)::character varying ||LPAD(EXTRACT(MONTH from date_stop_t)::character varying,2,'0')||LPAD(EXTRACT(DAY from date_stop_t)::character varying,2,'0')||'-'||rec.code into name_t;

	    select a.id into master_id_t from account_voucher a
	     where a.voucher_category_id=rec.voucher_category_id --系统自动产生
	       and a.name=name_t;

	    if coalesce(master_id_t,0)=0 then
		select nextval('account_voucher_id_seq') into master_id_t;
		insert into account_voucher(id,name,date,voucher_category_id,company_id,state,is_profit_loss_voucher,note,total_debit,total_credit,origin,period_id)
		values(master_id_t,name_t,date_stop_t::TIMESTAMP,rec.voucher_category_id,company_id_t,'draft','t',rec.name,rec.total_amount,rec.total_amount,rec.code||'-'||rec.name,period_id_t);
	    else
		delete from account_voucher_line where master_id=master_id_t;
		update account_voucher set state='draft',note=rec.name,total_debit=rec.total_amount,total_credit=rec.total_amount,origin=rec.code||'-'||rec.name
		 where id=master_id_t;
	    end if;
	       seq_t:=0;
	       for rec_line in select sequence,dc_type,account_id,amount,master_id
				  from account_voucher_template_line a
				 where a.master_id=rec.id
	       loop
		     insert into account_voucher_line(id,sequence,account_id,dc_type,
				  debit,credit,lc_debit,lc_credit,
				  master_id,company_id,currency_id,exchange_rate)
		     select nextval('account_voucher_line_id_seq'),rec_line.sequence,rec_line.account_id,rec_line.dc_type,
				   case when rec_line.dc_type='D' then rec_line.amount else 0 end as debit,
				   case when rec_line.dc_type='C' then rec_line.amount else 0 end as credit,
				   case when rec_line.dc_type='D' then rec_line.amount else 0 end as lc_debit,
				   case when rec_line.dc_type='C' then rec_line.amount else 0 end as lc_credit,
				   master_id_t,company_id_t,currency_id_t,1.0;

		    seq_t:=rec_line.sequence;
		end loop;
		insert into account_voucher_line(id,sequence,account_id,dc_type,
			  debit,credit,lc_debit,lc_credit,
			  master_id,company_id,currency_id,exchange_rate)
		select nextval('account_voucher_line_id_seq'),seq_t,rec.current_year_profit_account_id,rec.dc_type,
				   case when rec.dc_type='D' then rec.total_amount else 0 end as debit,
				   case when rec.dc_type='C' then rec.total_amount else 0 end as credit,
				   case when rec.dc_type='D' then rec.total_amount else 0 end as lc_debit,
				   case when rec.dc_type='C' then rec.total_amount else 0 end as lc_credit,
				   master_id_t,company_id_t,currency_id_t,1.0 ;
    end loop;

end;
$$ LANGUAGE plpgsql VOLATILE
        """)

        self._cr.execute("""


CREATE OR REPLACE FUNCTION public.create_account_voucher(
	period_id_t integer,
	uid_t integer)
    RETURNS void
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE
AS $BODY$
/*

select create_account_voucher(19,1)

select * from account_voucher

select * from account_period where id=19
alter table account_voucher_template add column material_account_id integer;
alter table account_voucher_template add column outsourcing_account_id integer;
alter table account_voucher_template add column esat_payable_account_id integer;


*/
declare
     --period_id_t integer:=111;
	   begin_date_t timestamp;
     end_date_t timestamp;
		 material_account_id_t integer;
		 outsourcing_account_id_t integer;
		 est_payable_account_id_t integer;
		 total_amount1_t numeric;
		 total_amount2_t numeric;
		 total_amount_t numeric;
		 seq integer:=1;
		 rec_t record;
		 company_id_t integer;
		 currency_id_t integer;
		 master_id_t integer;
		 voucher_category_id_t integer;
		 name_1_t character varying;
		 name_2_t character varying;
begin
   select id,currency_id into company_id_t,currency_id_t from res_company limit 1;
   select date_start at time zone 'UTC', date_stop at time zone 'UTC' into begin_date_t, end_date_t
	   from account_period
	  where id=period_id_t;

       select 'AP'||extract(year from  date_stop::timestamp)::text||lpad(extract(month from  date_stop::timestamp)::text,2,'0')||lpad(extract(day from  date_stop::timestamp)::text,2,'0')
					 into name_1_t
			from account_period
			where id=period_id_t;

		select 'AP'||extract(year from  date_stop::timestamp+'1 day')::text||lpad(extract(month from  date_stop::timestamp+'1 day')::text,2,'0')||lpad(extract(day from  date_stop::timestamp+'1 day')::text,2,'0')
					 into name_2_t
			from account_period
			where id=period_id_t;
		delete from account_voucher where name in (name_1_t,name_2_t);
	 select id into voucher_category_id_t from account_voucher_category	where name='预计凭证';
create temp table stock_t(
													name character varying,
													code character varying,
													amount numeric,
													note character varying
													);

create temp table stock_t2(
													name character varying,
													code character varying,
													amount numeric,
													note character varying
													);
select material_account_id,outsourcing_account_id,est_payable_account_id
  into material_account_id_t,outsourcing_account_id_t,est_payable_account_id_t
  from account_voucher_template
 where code='01';

insert into stock_t(name,code,amount,note)
select d.name,d.code,
       sum(COALESCE(a.local_amount,0)*c.effect_statement-COALESCE(a.invoiced_amount,0)*c.effect_statement) as amount,
			 '暂估材料'
 from stock_move a left join stock_picking b on a.picking_id=b.id
                   left join stock_picking_type c on b.picking_type_id=c.id
									 left join res_partner d on b.partner_id=d.id
where c.table_name in ('purchase_storage','purchase_storage_return')
  and b.state='done'
  and to_char(b.date_done,'yyyy-mm-dd') >=to_char(begin_date_t,'yyyy-mm-dd') and to_char(b.date_done,'yyyy-mm-dd')<=to_char(end_date_t,'yyyy-mm-dd')
	and abs(COALESCE(a.local_amount,0)*c.effect_statement-COALESCE(a.invoiced_amount,0)*c.effect_statement)>0
group by d.name,d.code;

insert into stock_t2(name,code,amount,note)
select d.name,d.code,
       sum(COALESCE(a.local_amount,0)*c.effect_statement-COALESCE(a.invoiced_amount,0)*c.effect_statement) as amount,
			 '加工费'
  from stock_move a left join stock_picking b on a.picking_id=b.id
                    left join stock_picking_type c on b.picking_type_id=c.id
										left join res_partner d on b.partner_id=d.id
where c.table_name in ('purchase_outsourcing_storage','purchase_outsourcing_storage_return')
  and b.state='done'
  and to_char(b.date_done,'yyyy-mm-dd') >=to_char(begin_date_t,'yyyy-mm-dd') and to_char(b.date_done,'yyyy-mm-dd')<=to_char(end_date_t,'yyyy-mm-dd')
	and abs(COALESCE(a.local_amount,0)*c.effect_statement-COALESCE(a.invoiced_amount,0)*c.effect_statement)>0
group by d.name,d.code;

select sum(coalesce(amount,0)) into total_amount1_t from stock_t;
select sum(coalesce(amount,0)) into total_amount2_t from stock_t2;
total_amount_t:=coalesce(total_amount1_t,0)+coalesce(total_amount2_t,0);
create temp table account_voucher_line_t(
																					master_id integer,
																					sequence integer,
																					dc_type  character varying,
																					account_id integer,
																					name character varying,
																					currency_id integer,
																					exchange_rate numeric,
																					debit numeric,
																					credit numeric,
																					lc_debit numeric,
																					lc_credit numeric,
																					state character varying,
																					company_id numeric
                                         );

if coalesce(total_amount_t,0)>0 then

   for rec_t in select * from stock_t
											  order by note,code
		 loop
			 seq:=1;
			   insert into account_voucher_line_t(sequence,dc_type,account_id,debit,credit,lc_debit,lc_credit,state,
				 company_id,currency_id,exchange_rate,name)
				 values(seq,'D',material_account_id_t,rec_t.amount,0,rec_t.amount,0,'draft',company_id_t,currency_id_t,1
				 ,rec_t.name||'-'||rec_t.code||'-'||rec_t.note);
			 seq:=seq+1;
		 end loop;

  for rec_t in select * from stock_t2
											  order by note,code
		 loop
			   insert into account_voucher_line_t(sequence,dc_type,account_id,debit,credit,lc_debit,lc_credit,
				        state,company_id,currency_id,exchange_rate,name)
				 values(seq,'D',outsourcing_account_id_t,rec_t.amount,0,rec_t.amount,0,
				        'draft',company_id_t,currency_id_t,1,rec_t.name||'-'||rec_t.code||'-'||rec_t.note);
			 seq:=seq+1;
		 end loop;

		insert into account_voucher_line_t(sequence,dc_type,account_id,debit,credit,lc_debit,lc_credit,
						state,company_id,currency_id,exchange_rate,name)
		values(seq,'C',est_payable_account_id_t,0,total_amount_t,0,total_amount_t,
					'draft',company_id_t,currency_id_t,1,'预付款');


	 insert into account_voucher (name,date,voucher_category_id,company_id,state,note,total_debit,total_credit)
	 values(name_1_t,end_date_t,voucher_category_id_t,company_id_t,'draft','AP预计凭证',total_amount_t,total_amount_t);

		SELECT currval(pg_get_serial_sequence('account_voucher', 'id')) into master_id_t;
		update account_voucher_line_t set master_id=master_id_t;

	 insert into account_voucher_line(master_id,sequence,dc_type,account_id,debit,credit,lc_debit,lc_credit,
				        company_id,currency_id,exchange_rate,name)
	 select master_id,sequence,dc_type,account_id,debit,credit,lc_debit,lc_credit,
				        company_id,currency_id,exchange_rate,name
		from account_voucher_line_t;
	 delete from account_voucher_line_t;
  --回转

	 insert into account_voucher (name,date,voucher_category_id,company_id,state,note,total_debit,total_credit)
	 values(name_2_t,end_date_t::timestamp + '1 day',voucher_category_id_t,company_id_t,'draft','AP预计回转凭证',total_amount_t,total_amount_t);

		SELECT currval(pg_get_serial_sequence('account_voucher', 'id')) into master_id_t;
    seq:=1;
		insert into account_voucher_line_t(master_id,sequence,dc_type,account_id,debit,credit,lc_debit,lc_credit,
						state,company_id,currency_id,exchange_rate,name)
		values(master_id_t,seq,'D',est_payable_account_id_t,total_amount_t,0,total_amount_t,0,
					'draft',company_id_t,currency_id_t,1,'预付款');

	 for rec_t in select * from stock_t
								 order by note,code
		 loop
			 seq:=2;
			   insert into account_voucher_line_t(master_id,sequence,dc_type,account_id,
				           debit,credit,lc_debit,lc_credit,state,company_id,currency_id,exchange_rate,name)
				 values(master_id_t,seq,'C',material_account_id_t,0,rec_t.amount,0,rec_t.amount,'draft',company_id_t,currency_id_t,1,
				        rec_t.name||'-'||rec_t.code||'-'||rec_t.note);
			 seq:=seq+1;
		 end loop;

  for rec_t in select * from stock_t2
											  order by note,code
		 loop
			   insert into account_voucher_line_t(master_id,sequence,dc_type,account_id,debit,credit,lc_debit,lc_credit,
				        state,company_id,currency_id,exchange_rate,name)
				 values(master_id_t,seq,'C',outsourcing_account_id_t,0,rec_t.amount,0,rec_t.amount,
				        'draft',company_id_t,currency_id_t,1,rec_t.name||'-'||rec_t.code||'-'||rec_t.note);
			 seq:=seq+1;
		 end loop;

	 insert into account_voucher_line(master_id,sequence,dc_type,account_id,debit,credit,lc_debit,lc_credit,
				       company_id,currency_id,exchange_rate,name)
	 select master_id,sequence,dc_type,account_id,debit,credit,lc_debit,lc_credit,
				  company_id,currency_id,exchange_rate,name
		from account_voucher_line_t;
end if;
drop table account_voucher_line_t;
drop table stock_t;
drop table stock_t2;

end;
$BODY$;

        """)

    # 数组拆分
    def list_split(self, items, n):
        return [items[i:i + n] for i in range(0, len(items), n)]

    def table_data(self):
        thead = {
            'total': '记账凭证',
            'done_date': self.done_date and fields.Date.from_string(self.done_date).strftime(
                '%Y/%m/%d') or fields.Date.from_string(fields.date.today()).strftime('%Y/%m/%d'),
            'company': self.company_id.name,
            'name': self.name,
        }
        rows = []
        credit_count = 0
        debit_count = 0
        for line in self.line_ids:
            if line.dc_type == "C":
                row = {
                    'account_name': line.account_id.complete_name,  # 总账科目
                    'name': line.name,  # 明细科目
                    'sub_account_lines_str': line.sub_account_lines_str,  # 摘要
                    'credit': line.credit,  # 贷方金额
                    'debit': 0,  # 借方金额
                }
                credit_count = credit_count + line.credit
                rows.append(row)
            else:
                if self.voucher_category_id.name == '暂估凭证' and len(line.move_ids) > 0:
                    for move_line in line.move_ids:
                        row = {
                            'account_name': line.account_id.complete_name,  # 总账科目
                            'sub_account_lines_str': move_line.name,  # 明细科目
                            'name': '',  # 摘要
                            'credit': 0,  # 贷方金额
                            'debit': move_line.amount_untaxed,  # 借方金额 >>> 未税金额
                            'lc_debit':move_line.amount_untaxed*move_line.exchange_rate,  # 借方本币金额 >>> 未税金额
                        }
                        debit_count = debit_count + move_line.amount_untaxed
                        rows.append(row)
                else:
                    row = {
                        'account_name': line.account_id.complete_name,  # 总账科目
                        'name': line.name,  # 明细科目
                        'sub_account_lines_str': line.sub_account_lines_str,  # 摘要
                        'credit': 0,  # 贷方金额
                        'debit': line.debit,  # 借方金额
                        'lc_debit': line.debit * line.exchange_rate,  # 借方本币金额 >>> 未税金额
                    }
                    debit_count = debit_count + line.debit
                    rows.append(row)
        tfoot = {
            'attachment_count': self.attachment_count,
            'create_user': self.create_uid.name,  # 制单
            'confirm_user': self.confirm_user_id.name,  # 出纳
            'done_user': self.done_user_id.name,  # 审核
            'manager_user': '',
        }
        body_list = self.list_split(rows, 8)
        table_list = []
        for body in body_list:
            body_size = len(body)
            if body_size < 8:
                for i in range(8 - body_size):
                    val = {
                        'account_name': '',
                        'name': '',
                        'sub_account_lines_str': '',
                        'credit': 0,  # 贷方金额
                        'debit': 0  # 借方金额
                    }
                    body.append(val)
            table = {
                'thead': thead,
                'tbody': body,
                'tfoot': tfoot,
                'size': len(body_list),
                'debit_count': debit_count,
                'credit_count': credit_count,
            }
            table_list.append(table)

        return table_list


class account_voucher_line(models.Model):
    _name = 'account.voucher.line'
    _description = u'凭证明细'

    @api.depends('account_id')
    def _get_sub_account_ids(self):
        for record in self:
            account_obj = self.env['cncw.account']
            record.sub_account_ids = account_obj.get_sub_account_ids(record.account_id)
            if not record.sub_account_lines and record.sub_account_type == 'has':
                for category_id in record.account_id.subaccount_category_ids:
                    record.sub_account_lines += record.sub_account_lines.create({
                        'category_id': category_id.id,
                        'voucher_line_id': record.id
                    })

    def _get_sub_account_id_required(self):
        if self.account_id and self.account_id.sub_account_type == 'has':
            return True
        else:
            return False

    def _domain_cash_id(self):
        # Add a domain on `picking_id` only if we come from a batch.
        subaccount_id = self.env['subaccount.category'].search([('code','=','cash_flow')],limit=1)

        if subaccount_id:
            return [
                ('subaccount_category_id', '=',subaccount_id.id)
            ]
    @api.depends("master_id")
    def _compute_related_field(self):
        if self.master_id:
            self.currency_id = self.master_id.partner_id.partner_currency_id
            self.exchange_rate = self.master_id.partner_id.partner_currency_id.rate or 1.0
        else:
            self.currency_id = self.env.user.company_id.currency_id
            self.exchange_rate = self.env.user.company_id.currency_id.rate or 1.0

    master_id = fields.Many2one('account.voucher', u'会计凭证', ondelete="cascade")
    sequence = fields.Integer(u'项次', default=1)
    dc_type = fields.Selection(base_cw.public.DC_TYPE, u'借贷', default='D')
    account_id = fields.Many2one('cncw.account', u'会计科目', required=False, ondelete="restrict")
    sub_account_id = fields.Many2one('res.partner', u'现金流项', ondelete="restrict",domain=lambda self:self._domain_cash_id())
    sub_account_lines = fields.One2many('sub.account.line', 'voucher_line_id', ondelete="restrict")
    sub_account_lines_str = fields.Char(string=u'会计辅助核算', compute='compute_sub_account_lines_str')

    def compute_sub_account_lines_str(self):
        for record in self:
            sub_account_lines_str = ''
            for line in record.sub_account_lines.filtered(lambda r: r.sub_account_id):
                sub_account_lines_str += ' | '+line.sub_account_id.name
                if line.category_id.code == 'cash_flow':
                    record.sub_account_id = line.sub_account_id
            record.sub_account_lines_str = sub_account_lines_str

    sub_account_type = fields.Selection(base_cw.public.SUB_ACCOUNT_TYPE, u'辅助核算类型',
                                        related='account_id.sub_account_type')
    sub_account_ids = fields.Many2many('res.partner', compute='_get_sub_account_ids', string=u'可选辅助核算')
    name = fields.Char(u'摘要')
    remark_id = fields.Many2one('account.voucher.remark', u'常用', ondelete="set null", store=False)
    currency_id = fields.Many2one('res.currency', u'币别', default='_compute_related_field')
    exchange_rate = fields.Float(u'汇率', digits='Exchange Rate', defualt=1.0)
    debit = fields.Float(u'借方金额', digits='Amount', default=0)
    credit = fields.Float(u'贷方金额', digits='Amount', default=0)

    lc_debit = fields.Float(u'本币借方金额', digits='Amount', default=0)
    lc_credit = fields.Float(u'本币贷方金额', digits='Amount', default=0)
    state = fields.Selection([('draft', u'草稿'),
                              ('confirmed', u'已确认'),
                              ('audited', u'已审核'),
                              ('cancel', u'已取消'), ], u'状态', related='master_id.state', readonly=True)
    company_id = fields.Many2one('res.company', string=u'公司', change_default=True,
                                 required=True, readonly=True, states={'draft': [('readonly', False)]},
                                 default=lambda self: self.env.company)
    report_seq = fields.Integer(u'科目序')
    balance = fields.Float(u'科目余额', digits='Amount', default=0)
    balance_id = fields.Many2one('account.balance', u'科目余额表', help=u'在总帐月结时写入', ondelete="set null")
    move_ids = fields.Many2many('stock.move', string=u'关联移动')

    #现金流量
    cash_seq = fields.Integer(u'现金项序')
    cash_balance = fields.Float(u'现金余额', digits='Amount', default=0)
    cash_monthly_id = fields.Many2one('account.cash.monthly', u'现金流量', help=u'在现金月结时写入', ondelete="set null")

    #  计算期初和余额
    @api.model
    def compute_begin_and_balance(self):
        begin_amount = 0.0

        if self.period_id and self.account_id:
            cash_dialy = self.env['account.voucher'].search(
                [('account_id', '=', self.account_id.id), ('period_id', '=', self.period_id.id),
                 ('state', 'in', ('confirmed', 'audited')), ('date', '<=', self.date), ('id', '!=', self.id)], )
            if len(cash_dialy) == 0:
                prior_period_id = self.period_id.pre_period_id.id or False
                begin_amount = prior_period_id and self.env['account.cash.monthly'].search(
                    [('account_id', '=', self.account_id.id), ('period_id', '=', prior_period_id)]).balance
            else:
                begin_amount = cash_dialy[-1].balance
        self.cash_balance = begin_amount + self.total_debit - self.total_credit

    def edit_sub_account_lines(self):
        return {
            "type": "ir.actions.act_window",
            "res_model": "account.voucher.line",
            'view_mode': 'form',
            'view_id': self.env.ref('cncw_ledger.view_account_voucher_line_form2').id,
            "res_id": self.id,
            "name": "编辑辅助核算",
            "target": 'new'
        }

    @api.constrains('debit')
    def _check_debit_debit(self):
        if (abs(self.debit) > 0 and abs(self.credit) > 0) or (abs(self.lc_debit) > 0 and abs(self.lc_credit) > 0):
            raise UserError(_(u'借贷方金额不可同时有值.'))

    def need_sub_account_id(self, account_id, sub_account_id=False):
        """判断会计科目是否需要辅助核算"""
        account = self.env['cncw.account'].browse([account_id])
        if account.sub_account_type == 'has' and not sub_account_id:
            raise UserError('会计科目%s需要辅助核算' % account.name)

    @api.model
    def create(self, vals):
        base_cw.public.generate_sequence(self, vals)
        # if vals.get('account_id',False):
        #     self.need_sub_account_id(vals.get('account_id'), vals.get('sub_account_lines_str', False))
        return super(account_voucher_line, self).create(vals)

    # @api.model
    # def default_get(self, fields):
    #     res = super(account_voucher_line, self).default_get(fields)
    #     if self._context:
    #         context_keys = self._context.keys()
    #         next_sequence = 1
    #         if 'line_ids' in context_keys:
    #             if len(self._context.get('line_ids')) > 0:
    #                 next_sequence = len(self._context.get('line_ids')) + 1
    #     res.update({'sequence': next_sequence})
    #     return res

    @api.onchange('dc_type')
    def onchange_dc_type(self):
        if self.dc_type == 'D':  # 借方
            self.lc_credit = self.credit = 0.0
        else:
            self.lc_debit = self.debit = 0.0

    @api.onchange('currency_id')
    def onchangge_currency_id(self):
        if self.currency_id:
            self.exchange_rate = self.currency_id.rate or 1.0
        else:
            self.exchange_rate = 1.0

    @api.onchange('remark_id')
    def onchangge_remark_id(self):
        if self.remark_id:
            self.name = self.remark_id.name

    @api.onchange('account_id')
    def onchangge_account_id(self):
        if self.account_id and self.account_id.sub_account_type != 'has':
            self.sub_account_id = False
        if self.account_id:
            self.compute_begin_and_balance()

    @api.onchange('exchange_rate')
    def onchange_exchange_rate(self):
        if self.exchange_rate != 0.0:
            self.lc_debit = self.debit * self.exchange_rate
            self.lc_credit = self.credit * self.exchange_rate
        else:
            self.lc_debit = self.debit
            self.lc_credit = self.credit

    @api.onchange('credit')
    def onchange_credit(self):
        if self.exchange_rate != 0.0:
            self.lc_credit = self.credit * self.exchange_rate
        else:
            self.lc_credit = self.credit

    @api.onchange('debit')
    def onchange_debit(self):
        if self.exchange_rate != 0.0:
            self.lc_debit = self.debit * self.exchange_rate
        else:
            self.lc_debit = self.debit

    def page(self, index):
        for recorde in self:
            if recorde:
                page = math.ceil((index + 1) / 8)
                return page

    def page_total(self, size):
        for recorde in self:
            if recorde:
                page_total = math.ceil(size / 8)
                return page_total

    def get_list(self, size):
        for recorde in self:
            if recorde:
                td = 8 - size % 8
                return range(0, td)

    # def write(self, vals):
    #     if vals.get('account_id', False):
    #         account = self.env['cncw.account'].browse(vals.get('account_id'))
    #         if account:
    #             if account.sub_account_type != 'has':
    #                 self.sub_account_id = False
    #             elif account.sub_account_type == 'has':
    #                 if not self.sub_account_lines:
    #                     raise UserError(_(u'会科%s 为有辅助核算会科,请输入辅助核算!' % (account.code,)))
    #     res = super(account_voucher_line, self).write(vals)
    #     return res
